package zy.dao.shop.program.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.program.ProgramDAO;
import zy.entity.shop.program.T_Shop_Program;
import zy.entity.shop.program.T_Shop_Program_Shop;
import zy.util.CommonUtil;

@Repository
public class ProgramDAOImpl extends BaseDaoImpl implements ProgramDAO {

	@Override
	public Integer count(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object sp_state = param.get("sp_state");
		Object sp_shop_code = param.get("sp_shop_code");
		Object shop_code = param.get(CommonUtil.SHOP_CODE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1) from (");
		sql.append(" select t.sp_id");
		sql.append(" from t_shop_program t ");
		sql.append(" join t_shop_program_shop sps on sps.sps_sp_id = t.sp_id and sps.companyid = t.companyid ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code=sps.sps_shop_code AND sp.companyid=sps.companyid ");
		sql.append(" where 1 = 1");
		if (null != sp_state && !"".equals(sp_state)) {
			sql.append(" and  sps.sps_state = :sp_state");
		}
		if (null != sp_shop_code && !"".equals(sp_shop_code)) {
			sql.append(" and  sps.sps_shop_code = :sp_shop_code");
		}
		if (null != begindate && !"".equals(begindate) && enddate != null && !"".equals(enddate)) {
			sql.append(" and DATE_FORMAT(t.sp_sysdate,'%Y-%m-%d') >=:begindate");
			sql.append(" and DATE_FORMAT(t.sp_sysdate,'%Y-%m-%d') <=:enddate");
		}
		
		if (shop_code != null && !shop_code.equals("")) {
			sql.append(" and (sps.sps_shop_code = :shop_code or sp.sp_upcode = :shop_code)");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" GROUP BY t.sp_id ");
		sql.append(") program_count");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Shop_Program> list(Map<String, Object> param) {
		Object start = param.get("start");
		Object end = param.get("end");
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object sp_state = param.get("sp_state");
		Object sp_shop_code = param.get("sp_shop_code");
		Object shop_code = param.get(CommonUtil.SHOP_CODE);
		StringBuffer sql = new StringBuffer("");
		/*sql.append(" select t.sp_id,t.sp_title,t.sp_info,t.sp_imgpath,t.sp_state,t.sp_us_id,t.sp_us_name,t.sp_sysdate,GROUP_CONCAT(sp.sp_name) AS sp_shop_name ");*/
		sql.append(" select t.sp_id,sps.sps_id,t.sp_title,t.sp_info,t.sp_imgpath,sps.sps_state,t.sp_us_id,t.sp_us_name,t.sp_sysdate,sp.sp_name AS sp_shop_name ");
		sql.append(" from t_shop_program t ");
		sql.append(" join t_shop_program_shop sps on sps.sps_sp_id = t.sp_id and sps.companyid = t.companyid ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code=sps.sps_shop_code AND sp.companyid=sps.companyid ");
		sql.append(" where 1 = 1");
		if (null != sp_state && !"".equals(sp_state)) {
			sql.append(" and  sps.sps_state = :sp_state");
		}
		if (null != sp_shop_code && !"".equals(sp_shop_code)) {
			sql.append(" and  sps.sps_shop_code = :sp_shop_code");
		}
		if (null != begindate && !"".equals(begindate) && enddate != null && !"".equals(enddate)) {
			sql.append(" and DATE_FORMAT(t.sp_sysdate,'%Y-%m-%d') >=:begindate");
			sql.append(" and DATE_FORMAT(t.sp_sysdate,'%Y-%m-%d') <=:enddate");
		}
		if (shop_code != null && !shop_code.equals("")) {
			sql.append(" and (sps.sps_shop_code = :shop_code or sp.sp_upcode = :shop_code)");
		}
		sql.append(" and t.companyid=:companyid");
		/*sql.append(" group by t.sp_id ");*/
		sql.append(" order by t.sp_id desc");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Shop_Program> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Program.class));
		return list;
	}

	@Override
	public void save(T_Shop_Program program) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" INSERT INTO t_shop_program");
		sql.append(" (sp_title,sp_info,sp_imgpath,sp_us_id,sp_us_name,sp_sysdate,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:sp_title,:sp_info,:sp_imgpath,:sp_us_id,:sp_us_name,:sp_sysdate,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(program),holder);
		program.setSp_id(holder.getKey().intValue());
		

		String sp_shop_codes = program.getSp_shop_code();//店铺编号
		String[] shop_codes = null; 
		if(sp_shop_codes !=null && !"".equals(sp_shop_codes)){
			shop_codes = sp_shop_codes.split(",");
		}
		
		//添加发送店铺
		List<T_Shop_Program_Shop> program_ShopList = new ArrayList<T_Shop_Program_Shop>();
		T_Shop_Program_Shop t_Shop_Program_Shop = null;
		for(int i=0;i<shop_codes.length;i++){
			t_Shop_Program_Shop = new T_Shop_Program_Shop();
			t_Shop_Program_Shop.setSps_sp_id(program.getSp_id());
			t_Shop_Program_Shop.setSps_state(0);
			t_Shop_Program_Shop.setSps_shop_code(shop_codes[i]);
			t_Shop_Program_Shop.setCompanyid(program.getCompanyid());
			program_ShopList.add(t_Shop_Program_Shop);
		}
		sql.setLength(0);
		sql.append(" insert into t_shop_program_shop ");
		sql.append(" (sps_sp_id,sps_shop_code,sps_state,companyid)");
		sql.append(" VALUES ");
		sql.append(" (:sps_sp_id,:sps_shop_code,:sps_state,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(program_ShopList.toArray()));
	}

	@Override
	public void del(Integer sp_id,Integer sps_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_program_shop");
		sql.append(" WHERE sps_id=:sps_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sps_id", sps_id));
		sql.setLength(0);
		sql.append("SELECT COUNT(1) FROM t_shop_program_shop WHERE 1=1 AND sps_sp_id = :sp_id ");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("sp_id", sp_id), Integer.class);
		if(count <= 0){
			sql.setLength(0);
			sql.append(" DELETE FROM t_shop_program");
			sql.append(" WHERE sp_id=:sp_id");
			namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sp_id", sp_id));
		}
	}

	public T_Shop_Program load(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		StringBuffer sql = new StringBuffer("");
		sql.append(" select t.sp_id,t.sp_title,t.sp_info,t.sp_imgpath,t.sp_us_id,t.sp_us_name,t.sp_sysdate,GROUP_CONCAT(sp.sp_name) AS sp_shop_name ");
		sql.append(" from t_shop_program t ");
		sql.append(" join t_shop_program_shop sps on sps.sps_sp_id = t.sp_id and sps.companyid = t.companyid ");
		sql.append(" JOIN t_base_shop sp ON sp.sp_code=sps.sps_shop_code AND sp.companyid=sps.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND t.sp_id = :sp_id");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Program.class)).get(0);
	}

	@Override
	public void update_state(Integer sps_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append("UPDATE t_shop_program_shop SET sps_state = 1 WHERE sps_id = :sps_id ");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("sps_id", sps_id));
	}
}
